﻿using QRCodeBluetooth.Helper;
using QRCodeBluetooth.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace QRCodeBluetooth.DAL
{
    public class RecordDAL
    {
        public int AddRecord(AppAuditRecord appAuditRecord)
        {
            string strsql = string.Format(@"INSERT INTO auditrecord (ownerAddress,ownerName,phone,applicationTime,projectId,identityInf) VALUES ('{0}', '{1}', '{2}', '{3}',{4},'{5}');select @@identity", appAuditRecord.ownerAddress, appAuditRecord.ownerName, appAuditRecord.phone, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), appAuditRecord.projectId, appAuditRecord.identityInf);
            return SqlHelper<OwnerInfo>.SqlScalar(strsql);
        }

        public bool UpdateRecord(AppAuditRecord appAuditRecord)
        {
            string strsql = string.Format(@"UPDATE auditrecord SET auditStatus=0,syncFlag=0,phone='{0}',applicationTime='{1}',ownerAddress='{2}',identityInf='{4}',ownerName='{5}' WHERE id={3}", appAuditRecord.phone, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), appAuditRecord.ownerAddress, appAuditRecord.id,appAuditRecord.identityInf,appAuditRecord.ownerName);
            return SqlHelper<OwnerInfo>.SqlExecute(strsql);
        }
        public List<RecordInfo> QueryRecord(RecordInfo recordInfo)
        {
            string strsql = "SELECT * FROM vi_record";

            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(recordInfo.Caption))
            {
                sqlwhere += " Caption like '%" + recordInfo.Caption + "%' AND";
            }
            if (recordInfo.AuditStatus > 0)
            {
                sqlwhere += " AuditStatus=" + recordInfo.AuditStatus + " AND";
            }
            if (!string.IsNullOrEmpty(recordInfo.QueryStartTime))
            {
                sqlwhere += " LastSetTime>='" + recordInfo.QueryStartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(recordInfo.QueryEndTime))
            {
                sqlwhere += " LastSetTime<='" + recordInfo.QueryEndTime + "' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strsql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (recordInfo.Recordhead > -1 && recordInfo.Recordtail > 0)
            {
                strsql += " LIMIT " + recordInfo.Recordhead + "," + recordInfo.Recordtail;
            }

            return SqlHelper<RecordInfo>.SqlList(strsql);
        }
        public int QueryRecordCount(RecordInfo recordInfo)
        {
            string strsql = "SELECT COUNT(1) FROM vi_record";

            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(recordInfo.Caption))
            {
                sqlwhere += " Caption like '%" + recordInfo.Caption + "%' AND";
            }
            if (recordInfo.AuditStatus > 0)
            {
                sqlwhere += " AuditStatus=" + recordInfo.AuditStatus + " AND";
            }
            if (!string.IsNullOrEmpty(recordInfo.QueryStartTime))
            {
                sqlwhere += " LastSetTime>='" + recordInfo.QueryStartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(recordInfo.QueryEndTime))
            {
                sqlwhere += " LastSetTime<='" + recordInfo.QueryEndTime + "' AND";
            }
            if (sqlwhere.Length > 0)
            {
                strsql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            CommFN<ProjectInfo> fn = new CommFN<ProjectInfo>();
            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }

        public bool DeleteRecord(string id)
        {
            string strsql = string.Format(@"DELETE FROM auditrecord WHERE id in(" + id + ")");
            CommFN<RecordInfo> fn = new CommFN<RecordInfo>();
            return SqlHelper<RecordInfo>.SqlExecute(strsql);
        }

        public OwnerInfo QueryOwnerID(string IdentityInf)
        {
            string strsql = "SELECT * FROM owner_info WHERE identityInf='" + IdentityInf + "'";

            return SqlHelper<OwnerInfo>.SqlSingleData(strsql);
        }

        public int QueryExistAudit(string IdentityInf, int projectid)
        {
            string strsql = "SELECT id FROM  auditrecord WHERE identityInf='" + IdentityInf + "' AND projectId=" + projectid;

            return SqlHelper<OwnerInfo>.SqlScalar(strsql);
        }

        public bool UpdateSyncFlag(int auditid)
        {
            string strSql = "UPDATE auditrecord SET syncFlag=1 WHERE id=" + auditid;
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

    }
}
